﻿using Npgsql;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Xml.Linq;

namespace DSSDataServiceGenerator.Model
{
    class PostgreSqlConnection
        : XObject,
            IConnection
    {
        public PostgreSqlConnection()
        {
            this.Freeze();

            //this.ConnectionString = "Server=150.151.250.17;Port=5432;User Id=User1;Password=abc123;Database=postgres;enlist=true";

            this.Server = "150.151.250.17";

            this.Port = 5432;

            this.Username = "User1";

            this.Password = "abc123";

            this.Database = "postgres";

            this.Enlist = true;

            this.Release();
        }

        public NpgsqlConnection _Connection = null;
        public IDbConnection Connection
        {
            get
            {
                if (this._Connection == null)

                    this._Connection = new NpgsqlConnection(string.Concat("Server=", this.Server, "; Port=", this.Port, "; User Id=", this.Username, "; Password=", this.Password, "; Database=", this.Database, "; enlist=", this.Enlist));

                return this._Connection;
            }
        }

        protected override void OnPropertyChanged()
        {
            this._Connection = null;

            base.OnPropertyChanged();
        }

        public string ConnectionType
        {
            get { return "PostgreSQL"; }
        }

        private string _Server;

        public string Server
        {
            get { return _Server; }
            set
            {
                _Server = value;

                base.OnPropertyChanged("Server");
            }
        }

        private int _Port;

        public int Port
        {
            get { return _Port; }
            set
            {
                _Port = value;

                base.OnPropertyChanged("Port");
            }
        }

        private string _Username;

        public string Username
        {
            get { return _Username; }
            set
            {
                _Username = value;

                base.OnPropertyChanged("Username");
            }
        }

        private string _Password;

        public string Password
        {
            get { return _Password; }
            set
            {
                _Password = value;

                base.OnPropertyChanged("Password");
            }
        }

        private string _Database;

        public string Database
        {
            get { return _Database; }
            set
            {
                _Database = value;

                base.OnPropertyChanged("Database");
            }
        }

        private bool _Enlist;

        public bool Enlist
        {
            get { return _Enlist; }
            set
            {
                _Enlist = value;

                base.OnPropertyChanged("Enlist");
            }
        }


        public XElement GenerateMetadata()
        {
            IDbCommand command = this.Connection.CreateCommand();

            command.CommandText = @"

Select	columns.table_schema
,	columns.table_name
,	columns.column_name
,	columns.is_identity
,	columns.ordinal_position
,	columns.data_type
,	coalesce(
		(
		Select	1
			From	information_schema.table_constraints
			Inner	Join
				information_schema.key_column_usage
			On	key_column_usage.constraint_name=	table_constraints.constraint_name
			And	key_column_usage.column_name	=	columns.column_name
			Where	columns.table_catalog	=	table_constraints.table_catalog
			And	columns.table_schema	=	table_constraints.table_schema
			And	columns.table_name	=	table_constraints.table_name
			And	table_constraints.constraint_type	=	'PRIMARY KEY'
			Limit (1)
		)
,	0
	)		As	IsPrimaryKey
	From	information_schema.columns
	Where	columns.table_schema	Not	In	(
		'pg_catalog'
	,	'information_schema'
	)
   ";

            DataTable result = new DataTable();

            command.Connection.Open();

            result.Load(command.ExecuteReader());

            command.Connection.Close();

            command.Connection.Dispose();

            command.Dispose();

            return new XElement(
                "Metadata"
            , new XAttribute("Name", "GeneratedDataService")
            , new XAttribute("Namespace", "ModernUIMVVMTemplate.Services")
            , result.Rows.Cast<DataRow>()
                .GroupBy(
                    (DataRow row) =>
                    {
                        return new { Namespace = Convert.ToString(row["table_schema"]), Name = Convert.ToString(row["table_name"]) };
                    }
                )
                .Select(
                    group =>
                    new XElement (
                        "Object"
                    , new XAttribute("Name", group.Key.Name)
                    , new XAttribute("Namespace", group.Key.Namespace)
                    , new XAttribute("IsSelected", "True")
                    , group
                    .OrderBy(
                        row =>
                        row["ordinal_position"]
                    )
                    .Select(
                            row =>
                            new XElement(
                                "Property"
                            , new XAttribute("Name", row["column_name"])
                            , new XAttribute("IsPrimaryKey", row["IsPrimaryKey"])
                            , new XAttribute("OrdinalPosition", row["ordinal_position"])
                            , new XAttribute("DataType", row["data_type"])
                            )
                        )
                    )
                )
            );
        }
    }
}
